<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>数据库应用系统</title>

    <!-- Latest compiled and minified CSS -->
    <link href="static/css/colors.css" rel="stylesheet" />
    <link href="static/css/style.css" rel="stylesheet" />
    <link href="static/layui-v2.5.6/layui/css/layui.css" rel="stylesheet" />
    <!--    <link href="static/fontawesome-free-5.13.0-web/css/fontawesome.css" rel="stylesheet">-->
    <link href="static/fontawesome-free-5.13.0-web/css/brands.css" rel="stylesheet">
    <link href="static/fontawesome-free-5.13.0-web/css/solid.css" rel="stylesheet">

    <!--load all styles -->

</head>

<body>
    <div style="height: 100%;width: 100%;position: fixed;background-color:dimgray;">
        <div style="position: absolute;top:0px;padding-top: 0px;width: 100%;z-index: 1;">
            <ul class="layui-nav" lay-filter="">
                <li class="layui-nav-item">
                    <h1 style="font-size: 24px;">chinook数据库可视化系统</h1>
                </li>
                <li style="margin-left: 20px;" class="layui-nav-item">
                    <a href="javascript:;" style="background-color: #111111;font-size: 16px;">问题</a>
                    <dl class="layui-nav-child" id="question_top">
                        <dd><a href="javascript:void(0);" data-sql='SELECT BillingCountry AS billingCountry, COUNT(*) AS Invoices FROM Invoice GROUP BY BillingCountry ORDER BY Invoices DESC; '>Question 1.1</a></dd>
                        <dd><a href="javascript:void(0);" data-sql='SELECT BillingCity AS billingCity, SUM(Total) AS InvoiceDollars FROM Invoice GROUP BY BillingCity ORDER BY InvoiceDollars DESC; '>Question 1.2</a></dd>
                        <dd><a href="javascript:void(0);" data-sql='SELECT CustomerId AS customerId, SUM(Total) AS money_spent FROM Invoice GROUP BY CustomerId ORDER BY money_spent DESC LIMIT 1; '>Question 1.3</a></dd>
                        <dd><a href="javascript:void(0);" data-sql='SELECT DISTINCT C.Email AS email, C.FirstName AS firstName, C.LastName AS lastName, G.Name AS name FROM Customer C JOIN Invoice I ON C.CustomerId = I.CustomerId JOIN InvoiceLine IL ON I.InvoiceId = IL.InvoiceId JOIN Track T ON IL.TrackId = T.TrackId JOIN Genre G ON T.GenreId = G.GenreId WHERE G.Name = "Rock" ORDER BY C.Email; '>Question 2.1</a></dd>
                        <dd><a href="javascript:void(0);" data-sql='SELECT Artist.ArtistId AS artistId, Artist.Name AS name, COUNT(Track.Name) AS Songs FROM Artist JOIN Album ON Album.ArtistId = Artist.ArtistId JOIN Track ON Album.AlbumId = Track.AlbumId JOIN Genre ON Track.GenreId = Genre.GenreId WHERE Genre.Name = "Rock" GROUP BY Artist.ArtistId, Artist.Name, Genre.Name ORDER BY Songs DESC LIMIT 10; '>Question 2.2</a></dd>
                        <dd><a href="javascript:void(0);" data-sql='SELECT a.Name AS name, SUM(il.Quantity * il.UnitPrice) AS AmountSpent, c.CustomerId AS customerId, c.FirstName AS firstName, c.LastName AS lastName FROM Artist a JOIN Album al ON a.ArtistId = al.ArtistId JOIN Track t ON t.AlbumId = al.AlbumId JOIN InvoiceLine il ON t.TrackId = il.Trackid JOIN Invoice i ON il.InvoiceId = i.InvoiceId JOIN Customer c ON c.CustomerId = i.CustomerId WHERE a.Name = "Iron Maiden"GROUP BY c.CustomerId ORDER BY AmountSpent DESC; '>Question 2.3</a></dd>
                        <dd><a href="javascript:void(0);" data-sql='WITH t1 AS ( SELECT COUNT(i.InvoiceId) Purchases, c.Country, g.Name, g.GenreId FROM Invoice i JOIN Customer c ON i.CustomerId = c.CustomerId JOIN InvoiceLine il ON il.Invoiceid = i.InvoiceId JOIN Track t ON t.TrackId = il.Trackid JOIN Genre g ON t.GenreId = g.GenreId GROUP BY c.Country, g.Name ORDER BY c.Country, Purchases DESC ) SELECT t1.* FROM t1 JOIN ( SELECT MAX(Purchases) AS MaxPurchases, Country, Name, GenreId FROM t1 GROUP BY Country )t2 ON t1.Country = t2.Country WHERE t1.Purchases = t2.MaxPurchases; '>Question 3.1</a></dd>
                        <dd><a href="javascript:void(0);" data-sql='SELECT Name, Milliseconds FROM (SELECT t.Name, t.Milliseconds, (SELECT AVG(Milliseconds) FROM Track) AS AvgLenght FROM Track t WHERE AvgLenght < t.Milliseconds ORDER BY t.Milliseconds DESC ); '>Question 3.2</a></dd>
                        <dd><a href="javascript:void(0);" data-sql='WITH t1 AS ( SELECT c.Country, SUM(i.Total) TotalSpent, c.FirstName, c.LastName, c.CustomerId FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId GROUP BY c.CustomerId ) SELECT t1.* FROM t1 JOIN( SELECT Country, MAX(TotalSpent) AS MaxTotalSpent, FirstName, LastName, CustomerId FROM t1 GROUP BY Country )t2 ON t1.Country = t2.Country WHERE t1.TotalSpent = t2.MaxTotalSpent ORDER BY Country;'>Question 3.3</a></dd>
                    </dl>
                </li>
                <li class="layui-nav-item"><a href="javascript:void(0);" id="button_readme">说明</a></li>
                <li class="layui-nav-item"><a href="javascript:void(0);" id="button_result_table">结果表</a></li>
                <li style="margin-left: 20px;" class="layui-nav-item"><button type="button" class="layui-btn layui-btn-normal" id="button_copy_sql">复制</button></li>
                <li style="padding-left: 5px;" class="layui-nav-item">
                    <h1 style="font-size: 18px;white-space: nowrap;max-width: 1px;">当前SQL: <span style="font-size: 12px;" id="sql_string">SELECT "hello world!" AS HELLO;</span></h1>
                </li>
            </ul>
        </div>
        <div style="position: absolute;top:0px;margin-top: 60px;width: 100%;height: calc(100% - 60px);z-index: 0;">
            <ul style="height: 100%;z-index: 1;overflow-y: auto;" class="layui-nav layui-nav-tree" lay-filter="test">
                <!-- 侧边导航: <ul class="layui-nav layui-nav-tree layui-nav-side"> -->
                <li class="layui-nav-item  layui-nav-itemed">
                    <a href="javascript:;">音乐</a>
                    <dl class="layui-nav-child node_set">
                        <dd><a href="javascript:void(0);" data-name="艺术家" data-type="Artist">艺术家</a></dd>
                        <dd><a href="javascript:void(0);" data-name="专辑" data-type="Album">专辑</a></dd>
                        <dd><a href="javascript:void(0);" data-name="艺术体裁" data-type="Genre">艺术体裁</a></dd>
                        <dd><a href="javascript:void(0);" data-name="播放列表" data-type="Playlist">播放列表</a></dd>
                    </dl>
                </li>
                <li class="layui-nav-item">
                    <a href="javascript:;">销售</a>
                    <dl class="layui-nav-child node_set">
                        <dd><a href="javascript:void(0);" data-name="雇员" data-type="Employee">雇员</a></dd>
                        <dd><a href="javascript:void(0);" data-name="客户" data-type="Customer">客户</a></dd>
                        <dd><a href="javascript:void(0);" data-name="发票" data-type="Invoice">发票</a></dd>
                    </dl>
                </li>
                <li class="layui-nav-item">
                    <a href="javascript:;">Basic question</a>
                    <dl class="layui-nav-child" id="question_left">
                        <dd><a href="javascript:void(0);" title="1. Provide a query showing Customers (just their full names, customer ID and country) who are not in the US." data-sql='SELECT FirstName || " " || LastName AS Name, CustomerId, Country FROM Customer WHERE Country <> "USA"; '>1. Provide a query showing Customers (just their full names, customer ID and country) who are not in the US.</a></dd>
                        <dd><a href="javascript:void(0);" title="2. Provide a query only showing the Customers from Brazil." data-sql='SELECT * FROM Customer WHERE Country IS "Brazil"; '>2. Provide a query only showing the Customers from Brazil.</a></dd>
                        <dd><a href="javascript:void(0);" title="3. Provide a query showing the Invoices of customers who are from Brazil. The resultant table should show the customer's full name, Invoice ID, Date of the invoice and billing country." data-sql='SELECT c.FirstName || " " || c.LastName as Name, i.InvoiceId, i.InvoiceDate, c.Country FROM Customer c LEFT JOIN Invoice i ON i.CustomerId = c.CustomerId WHERE c.Country IS "Brazil"; '>3. Provide a query showing the Invoices of customers who are from Brazil. The resultant table should show the customer's full name, Invoice ID, Date of the invoice and billing country.</a></dd>
                        <dd><a href="javascript:void(0);" title="4. Provide a query showing only the Employees who are Sales Agents." data-sql='SELECT * FROM Employee WHERE Title LIKE "%Sale%Agent%"; '>4. Provide a query showing only the Employees who are Sales Agents.</a></dd>
                        <dd><a href="javascript:void(0);" title="5. Provide a query showing a unique list of billing countries from the Invoice table." data-sql='SELECT BillingCountry FROM Invoice GROUP BY BillingCountry; '>5. Provide a query showing a unique list of billing countries from the Invoice table.</a></dd>
                        <dd><a href="javascript:void(0);" title="6. Provide a query showing the invoices of customers who are from Brazil." data-sql='SELECT i.InvoiceId, (e.FirstName || " " || e.LastName) as Name FROM Invoice i LEFT JOIN Customer c ON c.CustomerId = i.CustomerId LEFT JOIN Employee e ON c.SupportRepId = e.EmployeeId; '>6. Provide a query showing the invoices of customers who are from Brazil.</a></dd>
                        <dd><a href="javascript:void(0);" title="7. Provide a query that shows the invoices associated with each sales agent. The resultant table should include the Sales Agent's full name." data-sql='SELECT (c.FirstName || " " || c.LastName) as CustomerName, (e.FirstName || " " || e.LastName) as SalesAgentName, i.Total, i.BillingCountry FROM Invoice i LEFT JOIN Customer c ON c.CustomerId = i.CustomerId LEFT JOIN Employee e ON c.SupportRepId = e.EmployeeId; '>7. Provide a query that shows the invoices associated with each sales agent. The resultant table should include the Sales Agent's full name.</a></dd>
                        <dd><a href="javascript:void(0);" title="8. Provide a query that shows the Invoice Total, Customer name, Country and Sale Agent name for all invoices and customers." data-sql='SELECT (SELECT COUNT(*) FROM Invoice i WHERE SUBSTR(i.InvoiceDate,0,5) = "2009") AS Invoices09, (SELECT COUNT(*) FROM Invoice i WHERE SUBSTR(i.InvoiceDate,0,5) = "2011") AS Invoices11; '>8. Provide a query that shows the Invoice Total, Customer name, Country and Sale Agent name for all invoices and customers.</a></dd>
                        <dd><a href="javascript:void(0);" title="9. How many Invoices were there in 2009 and 2011? What are the respective total sales for each of those years?" data-sql='SELECT COUNT(*) FROM InvoiceLine il WHERE il.InvoiceId = 37; '>9. How many Invoices were there in 2009 and 2011? What are the respective total sales for each of those years?</a></dd>
                        <dd><a href="javascript:void(0);" title="10. Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for Invoice ID 37." data-sql='SELECT InvoiceId, COUNT(*) FROM InvoiceLine il GROUP BY InvoiceId; '>10. Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for Invoice ID 37.</a></dd>
                        <dd><a href="javascript:void(0);" title="11. Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for each Invoice." data-sql='SELECT t.Name, il.* FROM InvoiceLine il LEFT JOIN Track t ON t.TrackId = il.TrackId; '>11. Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for each Invoice.</a></dd>
                        <dd><a href="javascript:void(0);" title="12. Provide a query that includes the track name with each invoice line item." data-sql='SELECT t.Name as Song, ar.Name AS Artist, il.* FROM InvoiceLine il LEFT JOIN Track t ON t.TrackId = il.TrackId LEFT JOIN Album al ON al.AlbumId = t.AlbumId LEFT JOIN Artist ar ON ar.ArtistId = al.ArtistId; '>12. Provide a query that includes the track name with each invoice line item.</a></dd>
                        <dd><a href="javascript:void(0);" title="13. Provide a query that includes the purchased track name AND artist name with each invoice line item." data-sql='SELECT Invoice.BillingCountry, COUNT(*) FROM Invoice GROUP BY BillingCountry; '>13. Provide a query that includes the purchased track name AND artist name with each invoice line item.</a></dd>
                        <dd><a href="javascript:void(0);" title="14. Provide a query that shows the # of invoices per country. " data-sql='SELECT p.Name, COUNT(*) FROM Playlist p LEFT JOIN PlaylistTrack pt ON pt.PlaylistId = p.PlaylistId GROUP BY pt.PlaylistId; '>14. Provide a query that shows the # of invoices per country. </a></dd>
                        <dd><a href="javascript:void(0);" title="15. Provide a query that shows the total number of tracks in each playlist. The Playlist name should be include on the resultant table." data-sql='SELECT t.Name AS Song, a.Title as Album, mt.Name AS MediaType, g.Name AS Genre FROM Track t LEFT JOIN MediaType mt on mt.MediaTypeId = t.MediaTypeId LEFT JOIN Album a ON a.AlbumId = t.AlbumId LEFT JOIN Genre g ON g.GenreId = t.GenreId; '>15. Provide a query that shows the total number of tracks in each playlist. The Playlist name should be include on the resultant table.</a></dd>
                        <dd><a href="javascript:void(0);" title="16. Provide a query that shows all the Tracks, but displays no IDs. The resultant table should include the Album name, Media type and Genre." data-sql='SELECT i.*, COUNT(*) as LineCount FROM Invoice i LEFT JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId GROUP BY i.InvoiceId; '>16. Provide a query that shows all the Tracks, but displays no IDs. The resultant table should include the Album name, Media type and Genre.</a></dd>
                        <dd><a href="javascript:void(0);" title="17. Provide a query that shows all Invoices but includes the # of invoice line items." data-sql='SELECT e.FirstName || " " || e.lastName as Name, COUNT(*) AS Sales FROM Employee e JOIN Customer c ON c.SupportRepId = e.EmployeeId JOIN Invoice i ON i.CustomerId = c.CustomerId GROUP BY EmployeeId; '>17. Provide a query that shows all Invoices but includes the # of invoice line items.</a></dd>
                        <dd><a href="javascript:void(0);" title="18. Provide a query that shows total sales made by each sales agent." data-sql='SELECT e.FirstName || " " || e.lastName as Name, COUNT(*) AS Sales FROM Employee e JOIN Customer c ON c.SupportRepId = e.EmployeeId JOIN Invoice i ON i.CustomerId = c.CustomerId WHERE SUBSTR(i.InvoiceDate,0,5) = "2009" GROUP BY EmployeeId ORDER BY Sales DESC; '>18. Provide a query that shows total sales made by each sales agent.</a></dd>
                        <dd><a href="javascript:void(0);" title="19. Which sales agent made the most in sales in 2009?" data-sql='SELECT e.FirstName || " " || e.lastName as Name, COUNT(*) AS Sales FROM Employee e JOIN Customer c ON c.SupportRepId = e.EmployeeId JOIN Invoice i ON i.CustomerId = c.CustomerId WHERE SUBSTR(i.InvoiceDate,0,5) = "2010" GROUP BY EmployeeId ORDER BY Sales DESC; '>19. Which sales agent made the most in sales in 2009?</a></dd>
                        <dd><a href="javascript:void(0);" title="20. Which sales agent made the most in sales in 2010?" data-sql='SELECT e.FirstName || " " || e.lastName as Name, COUNT(*) AS Sales FROM Employee e JOIN Customer c ON c.SupportRepId = e.EmployeeId JOIN Invoice i ON i.CustomerId = c.CustomerId GROUP BY EmployeeId ORDER BY Sales DESC; '>20. Which sales agent made the most in sales in 2010?</a></dd>
                        <dd><a href="javascript:void(0);" title="21. Which sales agent made the most in sales over all?" data-sql='SELECT e.EmployeeId, e.FirstName || " " || e.LastName as Employee, COUNT(*) FROM Customer c JOIN Employee e ON e.EmployeeId = c.SupportRepId GROUP BY e.EmployeeId; '>21. Which sales agent made the most in sales over all?</a></dd>
                        <dd><a href="javascript:void(0);" title="22. Provide a query that shows the # of customers assigned to each sales agent." data-sql='SELECT BillingCountry, SUM(Total) FROM Invoice GROUP BY BillingCountry ORDER BY SUM(Total) DESC; '>22. Provide a query that shows the # of customers assigned to each sales agent.</a></dd>
                        <dd><a href="javascript:void(0);" title="23. Provide a query that shows the total sales per country. Which country's customers spent the most?" data-sql='SELECT t.TrackId, COUNT(*) FROM InvoiceLine il JOIN Track t ON t.TrackId = il.TrackId JOIN Invoice i ON i.InvoiceId = il.InvoiceId WHERE SUBSTR(i.InvoiceDate,0,5) = "2013" GROUP BY il.TrackId ORDER BY COUNT(*) DESC; '>23. Provide a query that shows the total sales per country. Which country's customers spent the most?</a></dd>
                        <dd><a href="javascript:void(0);" title="24. Provide a query that shows the most purchased track of 2013." data-sql='SELECT t.TrackId, COUNT(*) FROM InvoiceLine il JOIN Track t ON t.TrackId = il.TrackId GROUP BY il.TrackId ORDER BY COUNT(*) DESC; '>24. Provide a query that shows the most purchased track of 2013.</a></dd>
                        <dd><a href="javascript:void(0);" title="25. Provide a query that shows the top 5 most purchased tracks over all." data-sql='SELECT ar.Name, COUNT(*) FROM InvoiceLine il LEFT JOIN Track t ON t.TrackId = il.TrackId LEFT JOIN Album al ON al.AlbumId = t.AlbumId LEFT JOIN Artist ar ON ar.ArtistId = al.ArtistId GROUP BY ar.ArtistId ORDER BY COUNT(*) DESC LIMIT 3; '>25. Provide a query that shows the top 5 most purchased tracks over all.</a></dd>
                        <dd><a href="javascript:void(0);" title="26. Provide a query that shows the top 3 best selling artists." data-sql='SELECT mt.Name, COUNT(*) FROM InvoiceLine il LEFT JOIN Track t ON t.TrackId = il.TrackId LEFT JOIN MediaType mt ON mt.MediaTypeId = t.MediaTypeId GROUP BY t.MediaTypeId ORDER BY COUNT(*) DESC; '>26. Provide a query that shows the top 3 best selling artists.</a></dd>
                    </dl>
                </li>
                <li class="layui-nav-item">
                    <a href="javascript:;">设置</a>
                    <dl class="layui-nav-child settings">
                        <dd><a href="javascript:void(0);" data-name="最大子节点数" data-variable="MAX_CHILDREN">最大子节点数</a></dd>
                        <dd><a href="javascript:void(0);" data-name="最大表格记录数" data-variable="MAX_TABLE_RECORDS">最大表格记录数</a></dd>
                        <dd><a a href="https://github.com/DaviesGit" target="_blank">Powered by Davies</a></dd>
                    </dl>
                </li>
            </ul>
            <div style="position: absolute;top:0px;left:0px;margin-left: 200px;width: calc(100% - 200px);height: 100%;z-index: 0;">
                <div style="width: 100%;height: 100%;" id="tree">
                </div>
            </div>
        </div>
    </div>
    <div id="sql_result" style="display: none;">
        <div>
            <form style="margin: 5px 8px;" class="layui-form layui-form-pane" action="">
                <div class="layui-form-item">
                    <label class="layui-form-label" style="width:60px;">SQL</label>
                    <div class="layui-input-block" style="margin-left:60px; width: calc(100% - 60px);">
                        <input style="display: inline-block;margin-left:0px; width: calc(100% - 60px);" type="text" name="title" autocomplete="off" placeholder="请输入SQL语句" class="layui-input" id="sql_input"><button style="display: inline-block;margin-left:0px; width: 60px;vertical-align: baseline;" lay-submit lay-filter="sql_exec" class="layui-btn layui-btn-fluid" id="sql_exec">执行</button>
                    </div>
                </div>
            </form>
            <div style="margin: 0px 3px;">
                <table class="layui-hide" id="result_table"></table>
            </div>
        </div>
    </div>

    <!-- JS -->
    <script src="static/layui-v2.5.6/layui/layui.js"></script>
    <script src="static/js/d3.js"></script>
    <script src="static/js/flare-2.json.js"></script>
    <script src="static/js/config.js"></script>
    <script src="static/js/database.js"></script>
    <script src="static/js/main.js"></script>
    <script src="static/js/view.js"></script>
</body>

</html>
